<?php
class model_customer extends CI_Model {

	public function __construct()
	{
	//ini_set('memory_limit', '-1');
		$this->load->library('fclass');
		$this->load->database();
	}

	public function select($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->join('tbl_comp_custs', 'tbl_comp_custs.cust_id = tbl_customers.cust_id');
		$this->db->join('tbl_companies', 'tbl_companies.comp_id = tbl_comp_custs.comp_id');
		$this->db->join('tbl_cust_users', 'tbl_cust_users.cust_id = tbl_comp_custs.cust_id');

		if(isset($data["join_tbl_cust_users"])){
			$this->db->join('tbl_cust_users', 'tbl_cust_users.cust_id = tbl_customers.cust_id');
				if(isset($data["uId"])&&$data["uId"]!="")
				$this->db->where('tbl_cust_users.uId =',$data["uId"]);
		}


		if(isset($data["cust_creator_id"])&&$data["cust_creator_id"]!="")
			$data["array_uId"][]= $data["cust_creator_id"];

		if(isset($data["array_uId"])  && count($data["array_uId"])>0)
			$this->db->or_where_in('tbl_cust_users.uId', $data["array_uId"]); 

		if(isset($data["comp_status"])&&$data["comp_status"]!="")
			$this->db->where('comp_status !=',$data["comp_status"]);

		if(isset($data["cust_status"])&&$data["cust_status"]!="")
			$this->db->where('cust_status !=',$data["cust_status"]);

		if(isset($data["comp_id"])&&$data["comp_id"]!="")
			$this->db->where('tbl_companies.comp_id',$data["comp_id"]);

		if(isset($data["cust_id"])&&$data["cust_id"]!="")
			$this->db->where('tbl_customers.cust_id !=',$data["cust_id"]);
			
		if(isset($data["my_cust_id"])&&$data["my_cust_id"]!="")
			$this->db->where('tbl_customers.cust_id =',$data["my_cust_id"]);



		if(isset($data["where"])&&$data["where"]!="")
			$this->db->where("(
								cust_first_name LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR cust_sex LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR cust_last_name LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR cust_address LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								OR cust_phone LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								OR comp_name LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								)
		");

		if(isset($data["order_by"])&&$data["order_by"]!="")
			$this->db->order_by($data["order_by"]); 


		if(isset($data["limit"])&&$data["limit"]!="")
		$this->db->limit($data["limit"], $data["paging"]);
		
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}

	
	public function select1($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->join('tbl_comp_custs', 'tbl_comp_custs.cust_id = tbl_customers.cust_id');
		$this->db->join('tbl_companies', 'tbl_companies.comp_id = tbl_comp_custs.comp_id');
		
		if(isset($data["join_tbl_cust_users"])){
			$this->db->join('tbl_cust_users', 'tbl_cust_users.cust_id = tbl_customers.cust_id');
				if(isset($data["uId"])&&$data["uId"]!="")
				$this->db->where('tbl_cust_users.uId =',$data["uId"]);
		}


		if(isset($data["cust_creator_id"])&&$data["cust_creator_id"]!="")
			$data["array_uId"][]= $data["cust_creator_id"];

		if(isset($data["array_uId"])  && count($data["array_uId"])>0)
			$this->db->or_where_in('tbl_cust_users.uId', $data["array_uId"]); 

		if(isset($data["comp_status"])&&$data["comp_status"]!="")
			$this->db->where('comp_status !=',$data["comp_status"]);

		if(isset($data["cust_status"])&&$data["cust_status"]!="")
			$this->db->where('cust_status !=',$data["cust_status"]);

		if(isset($data["comp_id"])&&$data["comp_id"]!="")
			$this->db->where('tbl_companies.comp_id',$data["comp_id"]);

		if(isset($data["cust_id"])&&$data["cust_id"]!="")
			$this->db->where('tbl_customers.cust_id !=',$data["cust_id"]);
			
		if(isset($data["my_cust_id"])&&$data["my_cust_id"]!="")
			$this->db->where('tbl_customers.cust_id =',$data["my_cust_id"]);



		if(isset($data["where"])&&$data["where"]!="")
			$this->db->where("(
								cust_first_name LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR cust_sex LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR cust_last_name LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR cust_address LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								OR cust_phone LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								OR comp_name LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								)
		");

		if(isset($data["order_by"])&&$data["order_by"]!="")
			$this->db->order_by($data["order_by"]); 


		if(isset($data["limit"])&&$data["limit"]!="")
		$this->db->limit($data["limit"], $data["paging"]);
		
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}




	public function selectIdDetail($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');

		if(isset($data["join_companies"])){
			$this->db->join('tbl_comp_custs', 'tbl_comp_custs.cust_id = tbl_customers.cust_id');
			$this->db->join('tbl_companies', 'tbl_companies.comp_id = tbl_comp_custs.comp_id');
			
		}
		
		//if(isset)
		$this->db->where('tbl_customers.cust_id', $data["cust_id"]); 
		//$this->db->limit($data["limit"], $data["paging"]);
		$query = $this->db->get();
		//print_r($query);
		return $query->row_array();		
	}


	public function selectListConflict($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->join('tbl_cust_users', 'tbl_cust_users.cust_id = tbl_customers.cust_id');

		if(isset($data["uid"])&&$data["uid"]!="")
			$this->db->where('tbl_cust_users.uid', $data["uid"]); 
		
		//if(isset)
		$this->db->where('tbl_customers.cust_id', $data["cust_id"]); 
		//$this->db->limit($data["limit"], $data["paging"]);
		$query = $this->db->get();
		//print_r($query);
		return $query->result_array();		
	}

	public function selectIdConflict($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		//$this->db->join('tbl_comp_custs', 'tbl_comp_custs.comp_id = tbl_companies.comp_id');
		//$this->db->join('tbl_customers', 'tbl_customers.cust_id = tbl_comp_custs.cust_id');
		//if(isset)
		$this->db->where('cust_id', $data["cust_id"]); 
		//$this->db->limit($data["limit"], $data["paging"]);
		$query = $this->db->get();
		//print_r($query);
		return $query->result_array();		
	}

	

	public function selectCount($data)
	{
		//$this->db->select('*');
		//$this->db->from('tbl_companies');
		//$this->db->join('tbl_comp_custs', 'tbl_comp_custs.comp_id = tbl_companies.comp_id');
		//$this->db->join('tbl_customers', 'tbl_customers.cust_id = tbl_comp_custs.cust_id');
		//$this->db->where('cust_creator_id', $data["cust_creator_id"]); 
		//$query = $this->db->get();
		//return $query->num_rows();		
	}


	public function selectDetail($data)
	{		
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->join('tbl_cust_users', 'tbl_cust_users.cust_id = tbl_customers.cust_id');

		if(isset($data["cust_creator_id"])&&$data["cust_creator_id"]!="")
			$data["array_uId"][]= $data["cust_creator_id"];

		if(isset($data["array_uId"])&&count($data["array_uId"])>1)
			$this->db->or_where_in('uId', $data["array_uId"]); 

		$this->db->where('tbl_customers.cust_id', $data["cust_id"]); 
		$query = $this->db->get();
		return $query->result_array();		
	}


	public function selectDetailIdConflict($data)
	{		
		$this->db->select('*');
		$this->db->from('tbl_customers');

		$this->db->where('tbl_customers.cust_conflict_id', $data["cust_id"]); 
		$query = $this->db->get();
		return $query->result_array();		
	}


	public function selectToCompID($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->join('tbl_comp_custs', 'tbl_comp_custs.cust_id = tbl_customers.cust_id');
		$this->db->join('tbl_cust_users', 'tbl_cust_users.cust_id = tbl_comp_custs.cust_id');
		//$this->db->where('cust_creator_id', $data["cust_creator_id"]); 
		if(isset($data["cust_creator_id"]))
			$data["array_uId"][]=$data["cust_creator_id"];		

		if(count($data["array_uId"])>1)
			$this->db->where_in('tbl_cust_users.uId', $data["array_uId"]); 
		
		$this->db->where('comp_id', $data["comp_id"]); 
		$query = $this->db->get();
		return $query->result_array();
	}

	public function insert($data)
	{		
		$dt = $data["arrayCust"];
		$this->db->trans_start();
		$this->db->insert('tbl_customers', $dt); 
		$insert_id = $this->db->insert_id();
   		$this->db->trans_complete();
   		return $insert_id;
	}



	public function insertCompCust($data)
	{
		$this->db->insert('tbl_comp_custs', $data); 
	}

	public function insertCustUser($data)
	{
		$this->db->trans_start();
		$this->db->insert('tbl_cust_users', $data); 
		$insert_id = $this->db->insert_id();
   		$this->db->trans_complete();
   		return $insert_id;
		
	}

	public function updateCustUser($data,$id){
		$this->db->where('id', $id);
		$this->db->update('tbl_cust_users', $data); 
	}

	public function checkExists($data)
	{
		$query="SELECT * FROM `tbl_customers`
				WHERE (UPPER(cust_phone) =  ? 
				OR UPPER(cust_email) =  ? 
				OR (UPPER(cust_cmnd) = ? 
				AND cust_cmnd!='' ) 
				OR (UPPER(cust_tax_code) = ? 
				AND cust_tax_code!='' )) ";		

		$array=array(
					strtoupper($data["cust_phone"]),
					strtoupper($data["cust_email"]),
					strtoupper($data["cust_cmnd"]),
					strtoupper($data["cust_tax_code"])
					);

		if(isset($data["cust_status"])){
			$query.="AND `cust_status` != ? ";
			$array[]=$data["cust_status"];
		}

		if(isset($data["cust_creator_id"])){
			$query.="AND `cust_creator_id` = ? ";
			$array[]=$data["cust_creator_id"];
		}

		$result	= $this->db->query($query,$array);	
		return $result->result_array();
	}


	public function update($data,$id){
		$this->db->where('cust_id', $id);
		$this->db->update('tbl_customers', $data); 
	}

	public function updateConflict($data,$id,$cust_main_id){
		$this->db->where('cust_id', $id);
			if(isset($cust_main_id)&&$cust_main_id!="")
			$this->db->where('cust_id <>', $cust_main_id);

		$this->db->update('tbl_customers', $data); 
	}


	public function delete($data){
		$this->db->delete('tbl_customers', $data); 
	}

	public function deleteCompCust($data){
		$this->db->delete('tbl_comp_custs', $data); 
	}


	public function getListCustFull($data){
		$this->db->select('*');
		$this->db->from('tbl_comp_custs');
		$this->db->join("tbl_customers","tbl_customers.cust_id = tbl_comp_custs.cust_id");
		$this->db->join("tbl_companies","tbl_companies.comp_id = tbl_comp_custs.comp_id");
		$this->db->where('cust_status <>',"-1"); 
		$query = $this->db->get();
		return $query->result_array();
	}

	public function selectCompCust($data){
		$this->db->select('*');
		$this->db->from('tbl_comp_custs');
		$this->db->where('cust_id', $data['cust_id']); 
		$query = $this->db->get();
		return $query->result_array();
	}

	public function selectConflict($data)
	{		
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->where('cust_status', $data['cust_status']); 
		$this->db->where_in('cust_creator_id',$data['uId']); 
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();		
	}

	public function finance_line_select(){
		$this->db->select("*");
		$this->db->from("finance_lines");
		$this->db->where("line_status != ",0);
		$query = $this->db->get();
		return $query->result_array();
	}

	public function selectCountCust($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		if(isset($data["cust_creator_id"]))
			$this->db->where('cust_creator_id', $data['cust_creator_id']); 

		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->num_rows();
	}
	
	public function selectCust($data)
	{
		$this->db->select('*');
		$this->db->from('tbl_customers');
		$this->db->join("tbl_cust_users","tbl_cust_users.cust_id=tbl_customers.cust_id");
		if(isset($data["cust_id"]))
			$this->db->where('tbl_cust_users.cust_id', $data['cust_id']); 

		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}

	public function selectFinanceLines()
	{
		$this->db->select('*');
		$this->db->from('finance_lines');
		$this->db->where('line_status', 1); 

		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}


	public function selectSale($data){
		$this->db->select('*');
		$this->db->from('tblusers');
		$this->db->join("tbl_cust_users","tbl_cust_users.uId=tblusers.uId");
		if(isset($data["cust_id"])&&$data["cust_id"]!="")
			$this->db->where('cust_id', $data['cust_id']);

		if(isset($data["where"])&&$data["where"]!="")
			$this->db->where("(
								uFullname LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR uAddress LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR uPhone LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								)
		");

		if(isset($data["array_uId"])  && count($data["array_uId"])>0)
			$this->db->where_in('tbl_cust_users.uId', $data["array_uId"]);

		
		if(isset($data["order_by"])&&$data["order_by"]!="")
			$this->db->order_by($data["order_by"]); 

		if(isset($data["limit"])&&$data["limit"]!="")
		$this->db->limit($data["limit"], $data["paging"]);

		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}

	public function selectFullSale($data){
		$this->db->select('*');
		$this->db->from('tblusers');
		//$this->db->join("tbl_cust_users","tbl_cust_users.uId=tblusers.uId");
		if(isset($data["where"])&&$data["where"]!="")
			$this->db->where("(
								uFullname LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR uAddress LIKE '%".$this->fclass->cleanup_text($data["where"])."%' 
								OR uPhone LIKE '%".$this->fclass->cleanup_text($data["where"])."%'
								)
		");

		if(isset($data["array_uId"])  && count($data["array_uId"])>0)
			$this->db->where_in('uId', $data["array_uId"]);

		
		if(isset($data["order_by"])&&$data["order_by"]!="")
			$this->db->order_by($data["order_by"]); 

		if(isset($data["limit"])&&$data["limit"]!="")
		$this->db->limit($data["limit"], $data["paging"]);

		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}


	public function deleteCustUser($data){
		$this->db->delete('tbl_cust_users', $data); 
		//echo $this->db->last_query();
	}

	public function checkCustUserExists($data){
		$this->db->select('*');
		$this->db->from('tbl_cust_users');
		$this->db->where('cust_id',$data["cust_id"]);
		$this->db->where('uId',$data["uId"]);
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->num_rows();
	}


	public function checkExistsJoin($data)
	{
		$query="SELECT *
				FROM
				tbl_customers
				INNER JOIN tbl_cust_users ON tbl_customers.cust_id = tbl_cust_users.cust_id
				INNER JOIN tblusers ON tbl_cust_users.uId = tblusers.uId
				WHERE (UPPER(cust_phone) =  ? 
				OR UPPER(cust_email) =  ? 
				OR (UPPER(cust_cmnd) = ? 
				AND cust_cmnd!='' ) 
				OR (UPPER(cust_tax_code) = ? 
				AND cust_tax_code!='' )) 
				GROUP BY tbl_cust_users.uId";		

		$array=array(
					strtoupper($data["cust_phone"]),
					strtoupper($data["cust_email"]),
					strtoupper($data["cust_cmnd"]),
					strtoupper($data["cust_tax_code"])
					);

		if(isset($data["cust_status"])){
			$query.="AND `cust_status` != ? ";
			$array[]=$data["cust_status"];
		}

		if(isset($data["cust_creator_id"])){
			$query.="AND `cust_creator_id` = ? ";
			$array[]=$data["cust_creator_id"];
		}

		$result	= $this->db->query($query,$array);	
		return $result->result_array();
	}

	public function checkExistsJoinData($data)
	{
$query="SELECT *
				FROM
				tbl_customers
				INNER JOIN tbl_cust_users ON tbl_customers.cust_id = tbl_cust_users.cust_id						
				WHERE (UPPER(cust_phone) =  ? 
				OR UPPER(cust_email) =  ? 
				OR (UPPER(cust_cmnd) = ? 
				AND cust_cmnd!='' ) 
				OR (UPPER(cust_tax_code) = ? 
				AND cust_tax_code!='' ))
				AND cust_creator_id <> -1
				GROUP BY tbl_cust_users.uId";		
/*

		$query="SELECT *
				FROM
				tbl_customers
				INNER JOIN tbl_cust_users ON tbl_customers.cust_id = tbl_cust_users.cust_id	
				INNER JOIN tblusers ON tbl_cust_users.uId = tblusers.uId
				INNER JOIN tbl_app_permissions ON tbl_cust_users.uId = tbl_app_permissions.user_id		
				WHERE (UPPER(cust_phone) =  ? 
				OR UPPER(cust_email) =  ? 
				OR (UPPER(cust_cmnd) = ? 
				AND cust_cmnd!='' ) 
				OR (UPPER(cust_tax_code) = ? 
				AND cust_tax_code!='' ))
				AND cust_creator_id <> -1
				GROUP BY tblusers.uId";		
*/
		$array=array(
					strtoupper($data["cust_phone"]),
					strtoupper($data["cust_email"]),
					strtoupper($data["cust_cmnd"]),
					strtoupper($data["cust_tax_code"])
					);

	

		$result	= $this->db->query($query,$array);	
		//echo $this->db->last_query();
		return $result->result_array();
	}


}
	
?>